# -*- coding: utf-8-*-
import xmlrpclib
from flask import Flask, jsonify
from flask import g
from flaskext.openerp import OpenERP, Object
from flask_testing import TestCase
import unittest
import MySQLdb
import psycopg2
import psycopg2.extras
from contextlib import closing
import re

# #本地导入
class DefaultConfig(object):
    OPENERP_PROTOCOL = 'xmlrpc'
    OPENERP_HOSTNAME = 'crm.800890.com'
    OPENERP_DATABASE = 'dftg'
    OPENERP_DEFAULT_USER = 'admin'
    OPENERP_DEFAULT_PASSWORD = 'Dftgodoopwd'
    OPENERP_PORT = 80
    allow_none = True

    SECRET_KEY = 'secret_key'

    DEBUG = True

mysql = MySQLdb.connect(host="localhost", user="root", passwd="", db="oriental", charset="utf8")
postgre = psycopg2.connect(database="dftg", user="odoo", password="Dftgodoopwd800", host="crm.800890.com", port="65432")



#服务器导入
# class DefaultConfig(object):
#     OPENERP_PROTOCOL = 'xmlrpc'
#     OPENERP_HOSTNAME = 'crm.800890.com'
#     OPENERP_DATABASE = 'dftg'
#     OPENERP_DEFAULT_USER = 'admin'
#     OPENERP_DEFAULT_PASSWORD = 'Dftgodoopwd'
#     OPENERP_PORT = 8078
#     allow_none = True
#
#     SECRET_KEY = 'secret_key'
#
#     DEBUG = True
#
# mysql = MySQLdb.connect(host="rds36rbvnvb63ia.mysql.rds.aliyuncs.com", port=3306, user="nbzx", passwd="niqf5nxJ", db="oriental", charset="utf8")
# postgre = psycopg2.connect(database="dftg", user="dftg", password="Dftgodoopwd", host="crm.800890.com", port="5432")


app = Flask(__name__)
app.config.from_object(DefaultConfig())
OpenERP(app)


provinces = {
    '12': u'天津市',
    '31': u'上海市',
    '32': u'江苏省',
}

requirement_types = {
    "01": u"福利产品类",
    "02": u"商务礼品",
    "03": u"促销礼品",
    "04": u"渠道经销商",
    "05": u"会晤会展",
    "06": u"端午节气",
    "07": u"夏季防暑",
    "08": u"中秋卡券",
    "09": u"年货采购",
}


corp_kinds = {
    "01": u"机关单位",
    "02": u"国有企业",
    "03": u"民营企业",
    "04": u"合资企业",
    "05": u"外资企业",
    "06": u"其它",
}

industry_types = {
    "01": u"政府机构",
    "02": u"文体教育",
    "03": u"医疗系统",
    "04": u"金融系统",
    "05": u"电信行业",
    "06": u"汽车行业",
    "07": u"石油化工",
    "08": u"服务行业",
    "09": u"电力系统",
    "10": u"行业协会",
    "11": u"部队系统",
    "13": u"制造工业",
    "14": u"交通运输",
    "15": u"市政工程",
    "16": u"房产物业",
    "17": u"科技信息",
    "18": u"农林渔业",
    "12": u"其他",
}

festival_types = {
    6: '年货商品',
    7: '三八节推荐',
    8: '春茶上市',
    9: '端午节',
    10: '防暑降温',
    12: '中秋节',
    13: '德福隆-礼券',
    14: '德福隆-年货',
    15: '阿明-年货',
    16: '天喔-年货',
    17: '端午节',
    18: '有机杂粮',
    19: '生活日用品',
    20: '纺织用品',
}

#需要填写address_home_id，未完成！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
@app.route('/user')
def user():
    with closing(postgre.cursor(cursor_factory = psycopg2.extras.RealDictCursor)) as pg_cr:
        my_cr = mysql.cursor(MySQLdb.cursors.DictCursor)
        my_cr.execute("select * from sfc_user where enabled=1")
        res_users_proxy = Object(g.openerp_cnx, 'res.users')
        for row in my_cr.fetchall():
            username = row.get('username')
            values = {
                'name': row.get('name'),
                'login': username,
                'password': row.get('password'),
                'active': row.get('enabled') or False
            }
            pg_cr.execute("SELECT id FROM res_users WHERE login = '%s'" % username)
            user_row = pg_cr.fetchone()
            print username.encode('utf-8'), user_row
            if user_row:
                res_users_proxy.write(user_row.get('id'), values)
            else:
                res_users_proxy.create(values)
    return jsonify({'result': 'OK'})

@app.route('/customer')
def customer():
    with closing(postgre.cursor(cursor_factory = psycopg2.extras.RealDictCursor)) as pg_cr:
        my_cr = mysql.cursor(MySQLdb.cursors.DictCursor)
        my_cr.execute("select * from td_customer where deleted=0 AND op_name in ('水池')")
        res_partner_proxy = Object(g.openerp_cnx, 'res.partner')
        res_users_proxy = Object(g.openerp_cnx, 'res.users')
        res_customer_address_proxy = Object(g.openerp_cnx, 'customer.address')
        res_company_contacts_proxy = Object(g.openerp_cnx, 'company.contacts')
        for row in my_cr.fetchall():
            cust_code = row.get('cust_code')
            cust_full_name = row.get('cust_full_name')
            op_name = row.get('op_name')
            #=============================== =====================================
            #检查是否有相同客户编号
            pg_cr.execute("SELECT id FROM res_partner WHERE cust_code='%s' LIMIT 1" % (cust_code))
            exists_partner_row = pg_cr.fetchone()
            #如果存在相同的客户编号，则跳过新建
            exists_partner_id = None
            if exists_partner_row:
                exists_partner_id = exists_partner_row.get('id')
                print u' exists 同编号客户:', cust_code, cust_full_name.encode('utf-8'), exists_partner_id
                continue
            #检查是否有相同客户名称，如果有相同则改名为后缀＋1，检查2次
            pg_cr.execute("SELECT id FROM res_partner WHERE name='%s' LIMIT 1" % (cust_full_name))
            exists_partner_name_row = pg_cr.fetchone()
            if exists_partner_name_row:
                cust_full_name = cust_full_name + '1'
                pg_cr.execute("SELECT id FROM res_partner WHERE name='%s' LIMIT 1" % (cust_full_name))
                exists_partner_name_row = pg_cr.fetchone()
                if exists_partner_name_row:
                    cust_full_name = row.get('cust_full_name') + '2'

            #取得市县id
            city_or_county_name = row.get('city_code')
            city_id = None
            county_id = None
            if city_or_county_name:
                pg_cr.execute("SELECT id, city_id FROM all_county WHERE county_name like '" + city_or_county_name + "%'")
                county_row = pg_cr.fetchone()
                if county_row:
                    county_id = county_row.get('id')
                    city_id = county_row.get('city_id')
                else:
                    pg_cr.execute("SELECT id FROM all_city WHERE city_name like '" + city_or_county_name + "%'")
                    city_row = pg_cr.fetchone()
                    if city_row:
                        city_id = city_row.get('id')
            #取得省id
            province_name = provinces.get(row.get('province_code'))
            province_id = None
            if province_name:
                pg_cr.execute("SELECT id FROM res_country_state WHERE name='%s'" % (province_name))
                province_row = pg_cr.fetchone()
                if province_row:
                    province_id = province_row.get('id')
                elif city_id:
                    pg_cr.execute("SELECT province_id FROM all_city WHERE id = %s" % (city_id))
                    city_row = pg_cr.fetchone()
                    if city_row:
                        province_id = city_row.get('province_id')

            requirement_type_code = row.get('requirement_type')
            requirement_type = requirement_types.get(requirement_type_code)
            corp_kind_code = row.get('corp_kind')
            corp_kind = corp_kinds.get(corp_kind_code)
            industry_type_code = row.get('industry_type')
            industry_type = industry_types.get(industry_type_code)

            last_contact_date = row.get('last_contact_date')
            create_date = row.get('create_date')
            write_date = row.get('write_date')
            if not last_contact_date:
                last_contact_date = '1970-01-01'
            else:
                last_contact_date = str(last_contact_date)
            if not create_date:
                create_date = '1970-01-01'
            if not write_date:
                write_date = '1970-01-01'


            #新增或更新销售员用户表(暂时用login登录名来代替用户姓名，照道理在对应的res.partner表中)
            user_id = False
            if op_name:
                pg_cr.execute("SELECT id FROM res_users WHERE login='%s'" % (op_name))
                user_row = pg_cr.fetchone()
                if not user_row:
                    name = op_name
                    username = op_name
                    password = ''
                    active = False
                    my_cr.execute("select * from sfc_user where username='%s'" % (op_name))
                    old_user_row = my_cr.fetchone()
                    if old_user_row:
                        active = old_user_row.get('enabled') or False
                        password = old_user_row.get('password') or False
                        username = old_user_row.get('username')
                        name = old_user_row.get('name') or False

                    user_vals = {
                        'name': name,
                        'login': username,
                        'password': password,
                        'active': active
                    }
                    user_id = res_users_proxy.create(user_vals)
                else:
                    user_id = user_row.get('id')

            #新增客户资料
            values = {'name': cust_full_name,
                      'cust_code': cust_code,
                      'member_card': row.get('member_card') or False,
                      'is_company': True,
                      'phone': row.get('cust_phone') or False,
                      'fax': row.get('cust_fax') or False,
                      'province_id': province_id or False,
                      'city_id': city_id or False,
                      'county_id': county_id or False,
                      'street': row.get('address') or False,
                      'zip': row.get('postcode') or False,
                      'cust_grade': row.get('cust_grade') or False,
                      'comment': row.get('memo') or False,
                      'requirement_type': requirement_type or False,
                      'corp_kind': corp_kind or False,
                      'industry_type': industry_type or False,
                      'cust_source': row.get('cust_source') or False,
                      'introducer': row.get('introducer') or False,
                      'website': row.get('corp_website') or False,
                      'bank_name': row.get('bank_name') or False,
                      'bank_account': row.get('bank_account') or False,
                      'tax_no': row.get('tax_no') or False,
                      'registered_capital': row.get('registered_capital') or False,
                      'is_info_verify': row.get('is_info_verify') or False,
                      'user_id': user_id,
                      'last_contact_date': last_contact_date,
            }
            print '---- cust_full_name:', cust_full_name.encode('utf-8'), cust_code, exists_partner_id
            if not exists_partner_id:
                partner_id = res_partner_proxy.create(values)
            else:
                res_partner_proxy.write(exists_partner_id, values)

            #取得联系人
            my_cr.execute("select * from td_cust_linkman where cust_code='%s'" % cust_code)
            link_rows = my_cr.fetchall()
            for link_row in link_rows:
                contacts_name = link_row.get('link_name')
                #修正手机号码
                link_mobile = link_row.get('link_mobile')
                if link_mobile:
                    link_mobile = link_mobile.strip()
                address_vals = {
                    'partner_id': partner_id,
                    'province_id': province_id or False,
                    'city_id': city_id or False,
                    'county_id': county_id or False,
                    'zip': link_row.get('postcode') or False,
                    'default': link_row.get('primary_flag') or False,
                    'state': 'use',
                    'sexual': link_row.get('link_sex') or False,
                    'duties': link_row.get('link_duty') or False,
                    'department': link_row.get('link_department') or False,
                    'contacts': contacts_name,
                    'phone_number': link_row.get('link_tel') or False,
                    'mobile_number': link_mobile or False,
                    'home_phone': link_row.get('home_tel') or False,
                    'email': link_row.get('link_email') or False,
                    'street': link_row.get('link_address') or False,
                    'QQ': link_row.get('link_msn') or False,
                    'note': link_row.get('link_memo') or False,
                }

                link_birthday = str(row.get('link_birthday'))
                if not link_birthday or link_birthday == 'None':
                    link_birthday = False
                #重要联系人对应的partner
                partner_vals = {
                    'name': contacts_name,
                    'is_company': False,
                    'customer': False,
                    'supplier': False,
                    'contacts': True, #是联系人
                    'birthday': link_birthday,
                    'phone': link_row.get('link_tel') or False,
                    'mobile': link_mobile or False,
                    'zip': link_row.get('postcode') or False,
                    'email': link_row.get('link_email') or False,
                    'street': link_row.get('link_address') or False,
                    'QQ': link_row.get('link_msn') or False,
                    'comment': link_row.get('link_memo') or False,
                }
                #重要联系人"就职记录"
                contact_vlas = {
                    'partner_id': partner_id,
                    'position': link_row.get('link_duty') or False,
                    'department': link_row.get('link_department') or False,
                    'is_inauguratory': True,
                    'is_main_contacts': link_row.get('primary_flag') or False,
                    'mobile': link_mobile,
                    # 'phone': link_row.get('link_tel') or False,
                    # 'mobile': link_row.get('link_mobile') or False,
                    # 'email': link_row.get('link_email') or False,
                    # 'QQ': link_row.get('link_msn') or False,
                }
                #如果有存在手机号码而且手机号码正确，则作为重要联系人
                if link_mobile and len(link_mobile) == 11 and link_mobile.isdigit():
                    #检查手机号码是否重复
                    pg_cr.execute("SELECT id FROM res_partner  WHERE contacts=true AND mobile LIKE '%%%s%%'" % (link_mobile,))
                    exits_mobile_partner_row = pg_cr.fetchone()
                    if exits_mobile_partner_row:
                        exits_mobile_partner_id = exits_mobile_partner_row.get('id')
                    #检查是否有存在联系人
                    pg_cr.execute("SELECT cs.id, cs.contacts_id FROM company_contacts cs INNER JOIN res_partner rr ON cs.contacts_id=rr.id"\
                                  " WHERE cs.partner_id=%s AND rr.name='%s'" % (partner_id, contacts_name))
                    exists_contacts_row = pg_cr.fetchone()
                    if not exists_contacts_row:
                        #不存在联系人记录且手机号码不存在
                        if not exits_mobile_partner_row:
                            print '------ contacts:', contacts_name
                            #建立联系人的partner记录
                            print '------ partner_vals:', partner_vals
                            contacts_partner_id = res_partner_proxy.create(partner_vals)
                            contact_vlas.update({'contacts_id': contacts_partner_id})
                            print '------ contact_vlas:', contact_vlas
                            contacts_row_id = res_company_contacts_proxy.create(contact_vlas)
                        else:
                            print '---------- mobile exits:', link_mobile
                    else:
                        contacts_partner_id = exists_contacts_row.get('contacts_id')
                        print '------ contacts:', contacts_name, contacts_partner_id
                        res_partner_proxy.write(contacts_partner_id, partner_vals)
                        contacts_id = exists_contacts_row.get('id')
                        res_company_contacts_proxy.write(contacts_id, contact_vlas)
                else:
                    if link_mobile:
                        note = link_row.get('link_memo') or ''
                        note += '\n' + link_mobile
                        address_vals.update({'mobile_number': False, 'note': note})
                    address_ids = res_customer_address_proxy.search([('partner_id', '=', partner_id), ('contacts', '=', contacts_name)])
                    print '------ multi address:', contacts_name, address_ids, address_vals
                    if not address_ids:
                        address_id = res_customer_address_proxy.create(address_vals)
                    else:
                        address_id = address_ids[0]
                        #print '------ address_id:', address_id
                        res_customer_address_proxy.write(address_id, address_vals)

                #更新客户最后写入时间和建立时间
                pg_cr.execute("UPDATE res_partner SET create_date='%s', write_date='%s' WHERE id=%s" % (create_date, write_date, partner_id))
                #更新联系人表的最后更新时间
                # link_write_date = link_row.get('update_time')
                # if link_write_date:
                #     pg_cr.execute("UPDATE customer_address SET write_date='%s' WHERE id=%s" % (link_write_date, address_id))
                # if link_row.get('primary_flag'):
                #     pg_cr.execute("UPDATE customer_address SET \"default\"=FALSE WHERE partner_id=%s AND id<>%s" % (partner_id, address_id))
                postgre.commit()
            postgre.commit()


    #users = res_partner_proxy.select([], ['name'])
    return jsonify({'result': 'OK'})
    #return str(users)


@app.route('/supplier')
def supplier():
    with closing(postgre.cursor(cursor_factory = psycopg2.extras.RealDictCursor)) as pg_cr:
        my_cr = mysql.cursor(MySQLdb.cursors.DictCursor)
        my_cr.execute("select * from td_supplier where grade<>'不要'")
        for row in my_cr.fetchall():
            res_partner_proxy = Object(g.openerp_cnx, 'res.partner')
            res_customer_address_proxy = Object(g.openerp_cnx, 'customer.address')
            print row.get('supplier_name').encode('utf-8')
            supplier_code = row.get('supplier_code')
            values = {'name': row.get('supplier_name'),
                      'supplier_code': supplier_code,
                      'is_company': True,
                      'customer': False,
                      'supplier': True,
                      'supplier_grade': row.get('grade') or False,
                      'supplier_payment_type': row.get('pay_money_type') or False,
                      'supplier_delivery_type': row.get('trade_type') or False,
                      'phone': row.get('corp_phone') or False,
                      'fax': row.get('corp_fax') or False,
                      'street': row.get('corp_address') or False,
                      'website': row.get('corp_website') or False,
                      #'principal': row.get('linkman') or False,
                      'supplier_main_products': row.get('main_products') or False,
                      'supplier_quality_analysis': row.get('good_bad_parse') or False,
                      'supplier_has_invoice': row.get('Invoice') or False,
                      'bank_name': row.get('corp_bank_name') or False,
                      'bank_account': row.get('corp_bank_account') or False,
                      'tax_no': row.get('corp_tax_no') or False,
            }
            partner_ids = res_partner_proxy.search([('supplier_code', '=', supplier_code)])
            if not partner_ids:
                partner_id = res_partner_proxy.create(values)
            else:
                partner_id = partner_ids[0]
                res_partner_proxy.write(partner_id, values)
            #第一个联系人
            if row.get('linkman'):
                contacts = row.get('linkman')
                mobile = row.get('linkman_mobile')
                phone = row.get('linkman_phone') or ''
                if mobile:
                    if not re.match("\d{11}", mobile):
                        phone += "," + mobile
                        mobile = False
                linkman_vals = {
                    'partner_id': partner_id,
                    'contacts': contacts,
                    'mobile_number': mobile or False,
                    'phone_number': phone or False,
                    'email': row.get('linkman_email') or False,
                }
                address_ids = res_customer_address_proxy.search([('partner_id', '=', partner_id), ('contacts', '=', contacts)])
                print 'contacts1:', contacts, address_ids
                if not address_ids:
                    res_customer_address_proxy.create(linkman_vals)
                else:
                    res_customer_address_proxy.write(address_ids[0], linkman_vals)
            #第二个联系人
            if row.get('second_linkman'):
                contacts = row.get('second_linkman')
                linkman_vals = {
                    'partner_id': partner_id,
                    'contacts': contacts,
                    'phone_number': row.get('second_phone') or False,
                }
                address_ids = res_customer_address_proxy.search([('partner_id', '=', partner_id), ('contacts', '=', contacts)])
                print 'contacts2:', contacts, address_ids
                if not address_ids:
                    res_customer_address_proxy.create(linkman_vals)
                else:
                    res_customer_address_proxy.write(address_ids[0], linkman_vals)
            #第三个联系人
            if row.get('register_money') and row.get('register_address'):
                contacts = row.get('register_money')
                linkman_vals = {'partner_id': partner_id,
                                'contacts': contacts,
                                'phone': row.get('register_address')}
                address_ids = res_customer_address_proxy.search([('partner_id', '=', partner_id), ('contacts', '=', contacts)])
                print 'contacts3:', contacts, address_ids
                if not address_ids:
                    res_customer_address_proxy.create(linkman_vals)
                else:
                    res_customer_address_proxy.write(address_ids[0], linkman_vals)

    return jsonify({'result': 'OK'})


#产品分类
@app.route('/product_cate')
def product_cate():
    with closing(postgre.cursor(cursor_factory = psycopg2.extras.RealDictCursor)) as pg_cr:
        my_cr = mysql.cursor(MySQLdb.cursors.DictCursor)
        my_cr.execute("select * from tc_product_`category where caption<>'新分类' order by level")
        product_category_proxy = Object(g.openerp_cnx, 'product.category')
        root_categ_ids = product_category_proxy.search([('name', '=', 'Saleable')])
        if root_categ_ids:
            for row in my_cr.fetchall():
                categ_ids = product_category_proxy.search([('name', '=', row.get('caption'))])
                if categ_ids:
                    continue
                old_parent_id = row.get('parent_id')
                parent_id = None
                #如果是根分类，取得“可销售”分类（默认应该是id=2的那个吧）
                if old_parent_id == '0':
                    if root_categ_ids:
                        parent_id = root_categ_ids[0]
                else:
                    my_cr.execute("select * from tc_product_category where id='%s'" % (old_parent_id))
                    parent_categ_row = my_cr.fetchone()
                    if parent_categ_row:
                        parent_caption = parent_categ_row.get('caption')
                        parent_ids = product_category_proxy.search([('name', '=', parent_caption)])
                        if parent_ids:
                            parent_id = parent_ids[0]
                if parent_id:
                    vals = {
                        'name': row.get('caption'),
                        'parent_id': parent_id
                    }
                    print vals
                    product_category_proxy.create(vals)
    return jsonify({'result': 'OK'})

def get_exists_uom_id(prod_unit):
    with closing(postgre.cursor(cursor_factory = psycopg2.extras.RealDictCursor)) as pg_cr:
        pg_cr.execute("SELECT id FROM product_uom WHERE name='%s'" % (prod_unit))
        exists_unit_row = pg_cr.fetchone()
        if exists_unit_row:
            return exists_unit_row.get('id')
        else:
            pg_cr.execute("SELECT src FROM ir_translation WHERE name='product.uom,name' AND value='%s'" % (prod_unit))
            exists_unit_cn_row = pg_cr.fetchone()
            if exists_unit_cn_row:
                exists_unit_en = exists_unit_cn_row.get('src')
                pg_cr.execute("SELECT id FROM product_uom WHERE name='%s'" % (exists_unit_en))
                exists_rom_row = pg_cr.fetchone()
                exists_uom_id = exists_rom_row.get('id')
                return exists_uom_id


#计量单位
@app.route('/product_uom')
def product_uom():
    with closing(postgre.cursor(cursor_factory = psycopg2.extras.RealDictCursor)) as pg_cr:
        product_uom_proxy = Object(g.openerp_cnx, 'product.uom')
        my_cr = mysql.cursor(MySQLdb.cursors.DictCursor)
        my_cr.execute("select distinct prod_unit from td_product where prod_unit is not null and prod_unit<>''")
        prod_unit_rows = my_cr.fetchall()
        for row in prod_unit_rows:
            prod_unit = row.get('prod_unit')
            pg_cr.execute("SELECT id FROM product_uom WHERE name='%s'" % (prod_unit))
            exists_unit_row = pg_cr.fetchone()
            if exists_unit_row:
                continue
            pg_cr.execute("SELECT src FROM ir_translation WHERE name='product.uom,name' AND value='%s'" % (prod_unit))
            exists_unit_cn_row = pg_cr.fetchone()
            if exists_unit_cn_row:
                exists_unit_en = exists_unit_cn_row.get('src')
                pg_cr.execute("SELECT id FROM product_uom WHERE name='%s'" % (exists_unit_en))
                exists_rom_row = pg_cr.fetchone()
                exists_uom_id = exists_rom_row.get('id')
            else:
                vals = {
                    'name': prod_unit,
                    'category_id': 1
                }
                print vals
                product_uom_proxy.create(vals)
    return jsonify({'result': 'OK'})

#产品
@app.route('/products')
def products():
    with closing(postgre.cursor(cursor_factory = psycopg2.extras.RealDictCursor)) as pg_cr:
        my_cr = mysql.cursor(MySQLdb.cursors.DictCursor)
        product_category_proxy = Object(g.openerp_cnx, 'product.category')
        product_supplierinfo_proxy = Object(g.openerp_cnx, 'product.supplierinfo')
        root_categ_ids = product_category_proxy.search([('name', '=', 'Saleable')])
        if root_categ_ids:
            categ_id = root_categ_ids[0]
        else:
            raise Exception('not found category: Saleable')
        #my_cr.execute("select * from td_product where on_sale=1 AND (prod_code LIKE '2014%' OR prod_code LIKE '2015%' OR prod_code LIKE '2016%') ORDER BY prod_code")
        my_cr.execute("select * from td_product where prod_code in (select op.prod_code from td_order o inner join td_order_products op on o.uuid=op.order_uuid where DATE(o.order_date)>='2016-03-01' and o.flow_status>=0)")
        product_template_proxy = Object(g.openerp_cnx, 'product.template')
        index = 0
        for row in my_cr.fetchall():
            product_code = row.get('prod_code')
            pg_cr.execute("SELECT product_tmpl_id FROM product_product WHERE default_code = '%s'" % product_code)
            exists_product_row = pg_cr.fetchone()

            prod_name = row.get('prod_name')
            if prod_name:
                prod_name = prod_name.replace('', '')
            else:
                continue

            supplier_code = row.get('supplier_code')
            supplier_id = None
            pg_cr.execute("SELECT id FROM res_partner WHERE supplier_code = '%s'" % supplier_code)
            supplier_partner_row = pg_cr.fetchone()
            if supplier_partner_row:
                supplier_id = supplier_partner_row.get('id')
            category = row.get('child_category')
            if not category:
                category = row.get('medial_category')
                if not category:
                    category = row.get('main_category')
            if category:
                my_cr.execute("select * from tc_product_category where id='%s'" % (category))
                categ_row = my_cr.fetchone()
                if categ_row:
                    pg_cr.execute("SELECT id FROM product_category WHERE name = '%s'" % categ_row.get('caption'))
                    exists_categ_row = pg_cr.fetchone()
                    if exists_categ_row:
                        categ_id = exists_categ_row.get('id')

            uom_id = get_exists_uom_id(row.get('prod_unit'))
            if not uom_id:
                uom_id = 1

            on_sale_date = str(row.get('on_sale_date'))
            if not on_sale_date or on_sale_date == 'None':
                on_sale_date = False

            price_market = str(row.get('price_market'))
            if not price_market or price_market == 'None':
                price_market = False

            price_floor = str(row.get('price_floor'))
            if not price_floor or price_floor == 'None':
                price_floor = False

            price_base = str(row.get('price_base'))
            if not price_base or price_base == 'None':
                price_base = False

            price_vip = str(row.get('price_vip'))
            if not price_vip or price_vip == 'None':
                price_vip = False

            price_group = str(row.get('price_group'))
            if not price_group or price_group == 'None':
                price_group = False

            price_preferential = str(row.get('price_preferential'))
            if not price_preferential or price_preferential == 'None':
                price_preferential = False

            prod_points = str(row.get('prod_points'))
            if not prod_points or prod_points == 'None':
                prod_points = False

            prod_comment = row.get('prod_comment')
            if prod_comment:
                prod_comment = prod_comment.replace('', '')

            prod_model = row.get('prod_model')
            if prod_model:
                prod_model = prod_model.replace('', '')

            vals = {
                'name': prod_name or False,
                'default_code': row.get('prod_code'),
                'type': 'product',
                'categ_id': categ_id,
                'prod_brand': row.get('prod_brand') or False,
                'prod_model': prod_model or False,
                'prod_spec': row.get('prod_spec') or False,
                'prod_ingredient': row.get('prod_ingredient') or False,
                'prod_packaging': row.get('prod_packaging') or False,
                'list_price': price_group,
                'standard_price': price_base,
                'price_floor': price_floor,
                'price_vip': price_vip,
                'price_group': price_group,
                'price_preferential': price_preferential,
                'prod_points': prod_points,
                'least_order_num': row.get('least_order_num') or False,#需要放入供应商产品定义中
                'sale_ok': True,
                'uom_id': uom_id,
                'uom_po_id': uom_id,
                'pcs_qty': row.get('pre_box_num') or 0,
                'active': row.get('on_sale') or False,
                'on_sale_date': on_sale_date,
                'prod_comment': prod_comment or False,
                'description': row.get('inside_memo') or False,
                'stock_memo': row.get('stock_memo') or False,
                'max_provide_cycle': row.get('max_provide_cycle') or False,
                'min_provide_cycle': row.get('min_provide_cycle') or False,
                'is_spot': row.get('is_spot') or False,
                'festival': festival_types.get(row.get('festival_id')) or False,
            }
            print index, vals
            index += 1
            if exists_product_row:
                continue
                product_tmpl_id = exists_product_row.get('product_tmpl_id')
                product_template_proxy.write(product_tmpl_id, vals)
            else:
                product_tmpl_id = product_template_proxy.create(vals)
            if supplier_id:
                vals = {
                    'name': supplier_id,
                    'product_tmpl_id': product_tmpl_id
                }
                pg_cr.execute("SELECT id FROM product_supplierinfo WHERE product_tmpl_id=%s" % (product_tmpl_id))
                product_supplierinfo_row = pg_cr.fetchone()
                if product_supplierinfo_row:
                    product_supplierinfo_id = product_supplierinfo_row.get('id')
                    product_supplierinfo_proxy.write(product_supplierinfo_id, vals)
                else:
                    product_supplierinfo_proxy.create(vals)
    return jsonify({'result': 'OK'})


#订单
@app.route('/orders')
def orders():
    with closing(postgre.cursor(cursor_factory = psycopg2.extras.RealDictCursor)) as pg_cr:
        my_cr = mysql.cursor(MySQLdb.cursors.DictCursor)
        sale_order_proxy = Object(g.openerp_cnx, 'sale.order')
        sale_order_line_proxy = Object(g.openerp_cnx, 'sale.order.line')
        my_cr.execute("SELECT * FROM td_order WHERE DATE(order_date)>='2016-03-01' and flow_status>0 AND order_code IN ('', '')")
        for order_row in my_cr.fetchall():
            pg_cr.execute("SELECT id FROM res_users WHERE login='%s'" % order_row.get('op_name'))
            user_row = pg_cr.fetchone()
            if not user_row:
                print '------ not exists user:', order_row.get('op_name')
                break
            user_id = user_row.get('id')
            pg_cr.execute("SELECT id FROM res_partner WHERE cust_code='%s'" % order_row.get('cust_code'))
            partner_row = pg_cr.fetchone()
            if not partner_row:
                print '------ not exists partner:', order_row.get('cust_code')
                break

            partner_id = partner_row.get('id')
            pg_cr.execute("SELECT id FROM sale_order WHERE client_order_ref='%s'" % order_row.get('order_code'))
            exists_order_row = pg_cr.fetchone()
            print order_row.get('order_code'), user_id, exists_order_row
            money_sum_trim = str(order_row.get('money_sum_trim'))

            #订单行
            my_cr.execute("SELECT * FROM td_order_products WHERE order_uuid='%s'" % order_row.get('uuid'))
            origin_order_lines = []
            sequence = 10
            for origin_line in my_cr.fetchall():
                product_code = origin_line.get('prod_code')
                pg_cr.execute("SELECT id FROM product_product WHERE default_code = '%s'" % product_code)
                exists_product_row = pg_cr.fetchone()
                product_id = exists_product_row.get('id')
                line_vals = {
                    'name': origin_line.get('prod_name'),
                    'sequence': sequence,
                    'product_id': product_id,
                    'price_unit': str(origin_line.get('price_group')),
                    'product_uom_qty': origin_line.get('quantity'),
                    #'price_subtotal'
                    'state': 'draft',
                    'delay': '7',
                }
                sequence += 1
                origin_order_lines.append(line_vals)
            if origin_order_lines:
                #订单体
                order_vals = {
                    'client_order_ref': order_row.get('order_code'),
                    'state': 'draft',
                    'date_order': str(order_row.get('order_date')),
                    'create_date': str(order_row.get('order_date')),
                    'expected_delivery_time': str(order_row.get('delivery_date')),
                    'user_id': user_id,
                    'partner_id': partner_id,
                    'partner_invoice_id': partner_id,
                    'partner_shipping_id': partner_id,
                    'order_policy': 'manual',
                    'pricelist_id': 1,
                    'note': order_row.get('memo') or False,
                    'amount_untaxed': money_sum_trim or 0,
                    'amount_total': money_sum_trim or 0,
                    #'order_line': order_line,
                    'consignee': order_row.get('receiver') or False,
                    'street': order_row.get('delivery_address') or False,
                    'phone_number': order_row.get('receiver_phone') or False,
                    'mobile_number': order_row.get('receiver_mobile') or False,
                }
                if exists_order_row:
                    print '------- exists order', exists_order_row
                    continue
                    order_id = exists_order_row.get('id')
                    order_line = []
                    for line in origin_order_lines:
                        product_id = line.get('product_id')
                        pg_cr.execute("SELECT id FROM sale_order_line WHERE order_id=%s AND product_id=%s" % (order_id, product_id))
                        line_row = pg_cr.fetchone()
                        line_id = line_row.get('id')
                        order_line.append((1, line_id, line))
                    order_vals.update({'order_line': order_line})
                    sale_order_proxy.write(order_id, order_vals)
                else:
                    print '----- not exists, creating:', order_row.get('order_code')
                    order_line = []
                    for line in origin_order_lines:
                        order_line.append((0, 0, line))
                    order_vals.update({'order_line': order_line})
                    sale_order_proxy.create(order_vals)


    return jsonify({'result': 'OK'})



#联系记录
@app.route('/contact_record')
def contact_record():
    with closing(postgre.cursor(cursor_factory = psycopg2.extras.RealDictCursor)) as pg_cr:
        my_cr = mysql.cursor(MySQLdb.cursors.DictCursor)
        crm_phonecall_proxy = Object(g.openerp_cnx, 'crm.phonecall')
        my_cr.execute("select * from td_cust_contact_record where cust_code in (select cust_code from td_customer  where deleted=0 AND op_name IN ('水池')) and date(cont_date)>='2014-01-01';")
        for contact_row in my_cr.fetchall():
            cont_date = str(contact_row.get('cont_date'))
            op_name = contact_row.get('op_name') or False
            pg_cr.execute("SELECT id FROM res_users WHERE login='%s'" % op_name)
            user_row = pg_cr.fetchone()
            user_id = None
            if user_row:
                user_id = user_row.get('id')
            cust_code = contact_row.get('cust_code') or False
            pg_cr.execute("SELECT id FROM res_partner WHERE cust_code='%s'" % cust_code)
            partner_row = pg_cr.fetchone()
            partner_id = None
            if partner_row:
                partner_id = partner_row.get('id')

            #联系人名字放在部门字段
            department = contact_row.get('department') or ''
            link_name = contact_row.get('link_name') or ''
            if link_name:
                if department:
                    department += ' | '
                department += link_name

            if partner_id and contact_row.get('cont_memo'):
                cont_vals = {
                    'name': contact_row.get('cont_memo') or False,
                    'previous_contact_description': contact_row.get('cont_uuid'),
                    'date': cont_date,
                    'user_id': user_id or False,
                    'partner_id': partner_id or False,
                    'department': department,
                    'street': contact_row.get('address') or False,
                    'partner_phone': contact_row.get('link_tel') or False,
                    'duties': contact_row.get('link_duty') or False,
                    'contacts_name': contact_row.get('link_name') or False,
                    'contact_result_type': contact_row.get('ring_result') or False,
                    'contact_type': contact_row.get('cont_kind') or False,
                }
                #检查联系记录是否存在
                pg_cr.execute("SELECT id FROM crm_phonecall WHERE previous_contact_description='%s'" % contact_row.get('cont_uuid'))
                phone_call_row = pg_cr.fetchone()
                print '---- link_name, id', contact_row.get('cust_code'), link_name, phone_call_row
                id = None
                if phone_call_row:
                    id = phone_call_row.get('id')
                    crm_phonecall_proxy.write(id, cont_vals)
                else:
                    id = crm_phonecall_proxy.create(cont_vals)
                #更新联系记录的最后写入时间和建立时间
                pg_cr.execute("UPDATE crm_phonecall SET create_date='%s', write_date='%s' WHERE id=%s" % (cont_date, cont_date, id))
                postgre.commit()

    return jsonify({'result': 'OK'})


#转移收货地址到重要联系人中
@app.route('/address_moveto_contacts')
def address_moveto_contacts():
    with closing(postgre.cursor(cursor_factory = psycopg2.extras.RealDictCursor)) as pg_cr:
        res_partner_proxy = Object(g.openerp_cnx, 'res.partner')
        res_customer_address_proxy = Object(g.openerp_cnx, 'customer.address')
        res_company_contacts_proxy = Object(g.openerp_cnx, 'company.contacts')
        pg_cr.execute("SELECT * FROM customer_address ORDER BY  partner_id, \"default\" DESC")
        for address_row in pg_cr.fetchall():
            customer_address_id = address_row.get('id')
            partner_id = address_row.get('partner_id')
            contacts_name = address_row.get('contacts')
            print '---- customer.address: ', contacts_name
            birthday = str(address_row.get('birthday'))
            if not birthday or birthday == 'None':
                birthday = False

            home_phone = address_row.get('home_phone')
            if home_phone:
                home_phone = u'原家庭电话栏:' + home_phone.decode('utf-8')
            else:
                home_phone = ''
            #收货地址是否转换为主联系人
            is_main_contacts = address_row.get('is_main_contacts')
            if is_main_contacts:
                #检查对应的企业客户是否有主联系人
                pg_cr.execute("SELECT main_contacts FROM res_partner WHERE id=%s" % (address_row.get('partner_id')))
                company_customer_partner_row = pg_cr.fetchone()
                main_contacts = company_customer_partner_row.get('company_customer_partner_row')
                if main_contacts:
                    is_main_contacts = False
            #检查手机号码是否重复，如果重复放入fax传真
            mobile_number = address_row.get('mobile_number')
            if mobile_number:
                mobile_number = mobile_number.strip()
                pg_cr.execute("SELECT id FROM res_partner WHERE mobile='%s' AND contacts=TRUE" % (mobile_number))
                exists_mobile_contacts_row = pg_cr.fetchone()
                if exists_mobile_contacts_row:
                    if home_phone:
                        home_phone = home_phone + ', '
                    home_phone = home_phone + u'重复的手机号码:' + mobile_number
                    mobile_number = False
                    print '---- home_phone: ', home_phone
            #检查客户的主联系人是否有值，如果有值，就不要替换
            is_main_contacts = address_row.get('default')
            if is_main_contacts:
                pg_cr.execute("SELECT main_contacts FROM res_partner WHERE id=%s" % (partner_id))
                partner_row = pg_cr.fetchone()
                if partner_row.get('main_contacts'):
                    is_main_contacts = False

            #重要联系人对应的partner
            partner_vals = {
                'parent_id': partner_id,
                'name': contacts_name,
                'is_company': False,
                'customer': False,
                'supplier': False,
                'contacts': True, #是联系人
                'birthday': birthday,
                'phone': address_row.get('phone_number') or False,
                'mobile': mobile_number or False,
                'zip': address_row.get('zip') or False,
                'email': address_row.get('email') or False,
                'street': address_row.get('street') or False,
                'QQ': address_row.get('QQ') or False,
                'comment': address_row.get('note') or False,
                'fax': home_phone or False, #没有家庭电话字段，放入传真字段
                'gender': address_row.get('sexual') or False,
                'title': address_row.get('title') or False,
                'user_id': address_row.get('user_id') or False,
            }

            #重要联系人"就职记录"
            contact_vlas = {
                'partner_id': partner_id,
                'position': address_row.get('duties') or False,
                'department': address_row.get('department') or False,
                'is_inauguratory': True,
                'is_main_contacts': is_main_contacts or False,
                'is_subscription': address_row.get('is_subscription') or False,
                'mobile': mobile_number or False,
                # 'phone': link_row.get('link_tel') or False,
                # 'mobile': link_row.get('link_mobile') or False,
                # 'email': link_row.get('link_email') or False,
                # 'QQ': link_row.get('link_msn') or False,
            }
            #----- 建立联系人partner记录和就职记录
            contacts_partner_id = res_partner_proxy.create(partner_vals)
            contact_vlas.update({'contacts_id': contacts_partner_id})
            print '------ contact_vlas:', contact_vlas
            contacts_row_id = res_company_contacts_proxy.create(contact_vlas)
            #删掉收货地址
            if contacts_row_id:
                res_customer_address_proxy.unlink(customer_address_id)

    return jsonify({'result': 'OK'})

#老系统余下客户(蔬菜、第二批)导入到线索(工商不需要导入)
@app.route('/leads')
def leads():
    with closing(postgre.cursor(cursor_factory = psycopg2.extras.RealDictCursor)) as pg_cr:
        crm_lead_proxy = Object(g.openerp_cnx, 'crm.lead')
        my_cr = mysql.cursor(MySQLdb.cursors.DictCursor)
        my_cr.execute("select * from td_customer where deleted=0 AND op_name IN ('蔬菜', '第二批') and cust_full_name is not null and cust_full_name <>'' LIMIT 43000, 20000")
        for row in my_cr.fetchall():
            cust_code = row.get('cust_code')
            my_cr.execute("SELECT * FROM td_cust_linkman WHERE cust_code=%s" % (cust_code))
            linkman_rows = my_cr.fetchall()
            linkman_count = len(linkman_rows)

            cust_full_name = row.get('cust_full_name')
            contact_name = row.get('linkman') or u'负责人'
            cust_phone = row.get('cust_phone')
            cust_fax = row.get('cust_fax')
            address = row.get('address')
            postcode = row.get('postcode')
            memo = row.get('memo') or ''
            email_from = False
            QQ = False
            mobile = False
            department = False
            function = False
            birthday = False
            gender = False

            #暂不处理联系人大于1个的客户
            if linkman_count > 1:
                continue
            elif linkman_count == 1:
                linkman_row = linkman_rows[0]
                if linkman_row.get('link_name'):
                    contact_name = linkman_row.get('link_name')
                if linkman_row.get('link_tel'):
                    cust_phone = linkman_row.get('link_tel')
                if linkman_row.get('link_fax'):
                    cust_fax = linkman_row.get('link_fax')
                if linkman_row.get('link_address'):
                    address = linkman_row.get('link_address')
                if linkman_row.get('postcode'):
                    postcode = linkman_row.get('postcode')
                if linkman_row.get('link_memo'):
                    if memo:
                        memo = memo + '\n' + linkman_row.get('link_memo')
                    else:
                        memo = linkman_row.get('link_memo')
                if linkman_row.get('link_email'):
                    email_from = linkman_row.get('link_email')
                if linkman_row.get('link_msn'):
                    QQ = linkman_row.get('link_msn')
                if linkman_row.get('link_mobile'):
                    mobile = linkman_row.get('link_mobile')
                if linkman_row.get('link_department'):
                    department = linkman_row.get('link_department')
                if linkman_row.get('link_duty'):
                    function = linkman_row.get('link_duty')
                if linkman_row.get('link_birthday'):
                    birthday = str(linkman_row.get('link_birthday'))
                if linkman_row.get('link_sex'):
                    gender = linkman_row.get('link_sex')
            else:
                print '----------- no linkman'

            #=============================== =====================================
            #检查是否有相同客户编号
            pg_cr.execute("SELECT id FROM crm_lead WHERE cust_code='%s' LIMIT 1" % (cust_code))
            exists_lead_row = pg_cr.fetchone()
            #如果存在相同的客户编号，则跳过新建
            exists_lead_id = None
            if exists_lead_row:
                exists_lead_id = exists_lead_row.get('id')
                print u' exists 同编号客户线索，不覆盖:', cust_code, cust_full_name.encode('utf-8'), exists_lead_id
                continue

            #取得市县id
            city_or_county_name = row.get('city_code')
            city_id = None
            county_id = None
            if city_or_county_name:
                pg_cr.execute("SELECT id, city_id FROM all_county WHERE county_name like '" + city_or_county_name + "%'")
                county_row = pg_cr.fetchone()
                if county_row:
                    county_id = county_row.get('id')
                    city_id = county_row.get('city_id')
                else:
                    pg_cr.execute("SELECT id FROM all_city WHERE city_name like '" + city_or_county_name + "%'")
                    city_row = pg_cr.fetchone()
                    if city_row:
                        city_id = city_row.get('id')
            #取得省id
            province_name = provinces.get(row.get('province_code'))
            province_id = None
            if province_name:
                pg_cr.execute("SELECT id FROM res_country_state WHERE name='%s'" % (province_name))
                province_row = pg_cr.fetchone()
                if province_row:
                    province_id = province_row.get('id')
                elif city_id:
                    pg_cr.execute("SELECT province_id FROM all_city WHERE id = %s" % (city_id))
                    city_row = pg_cr.fetchone()
                    if city_row:
                        province_id = city_row.get('province_id')


            last_contact_date = row.get('last_contact_date')
            create_date = row.get('create_date')
            write_date = row.get('write_date')
            if not last_contact_date:
                last_contact_date = False
            else:
                last_contact_date = str(last_contact_date)
            if not create_date:
                create_date = '1970-01-01'
            if not write_date:
                write_date = '1970-01-01'



            #新增lead资料
            values = {'name': cust_full_name,
                      'partner_name': cust_full_name,
                      'cust_code': cust_code,
                      'contact_name': contact_name,
                      'phone': cust_phone or False,
                      'mobile': mobile or False,
                      'fax': cust_fax or False,
                      'province_id': province_id or False,
                      'city_id': city_id or False,
                      'county_id': county_id or False,
                      'street': address or False,
                      'zip': postcode or False,
                      'description': memo or False,
                      'referred': row.get('introducer') or False,
                      'last_contact_date': last_contact_date,
                      'email_from': email_from or False,
                      'QQ': QQ or False,
                      'department': department or False,
                      'function': function or False,
                      'birthday': birthday or False,
                      'gender': gender or False,
                      'create_date': create_date or False,
                      'write_date': write_date or False,
                      'user_id': False,
            }
            print values
            lead_id = False
            if not exists_lead_id:
                lead_id = crm_lead_proxy.create(values)
            else:
                lead_id = exists_lead_id
                crm_lead_proxy.write(exists_lead_id, values)

            print cust_code, cust_full_name.encode('utf-8'), lead_id

    return jsonify({'result': 'OK'})


#线索联系记录
@app.route('/lead_contact_record')
def lead_contact_record():
    with closing(postgre.cursor(cursor_factory = psycopg2.extras.RealDictCursor)) as pg_cr:
        my_cr = mysql.cursor(MySQLdb.cursors.DictCursor)
        crm_phonecall_proxy = Object(g.openerp_cnx, 'crm.phonecall')
        my_cr.execute("select * from td_cust_contact_record where cust_code in (select cust_code from td_customer  where deleted=0 AND op_name IN ('蔬菜', '第二批')) and date(cont_date)>='2014-01-01' ORDER BY cont_date DESC")
        for contact_row in my_cr.fetchall():
            cont_date = str(contact_row.get('cont_date'))
            op_name = contact_row.get('op_name') or False
            pg_cr.execute("SELECT id FROM res_users WHERE login='%s'" % op_name)
            user_row = pg_cr.fetchone()
            user_id = None
            if user_row:
                user_id = user_row.get('id')
            cust_code = contact_row.get('cust_code') or False
            pg_cr.execute("SELECT id FROM crm_lead WHERE cust_code='%s'" % cust_code)
            lead_row = pg_cr.fetchone()
            lead_id = None
            if lead_row:
                lead_id = lead_row.get('id')
            else:
                print '-------- no lead exits', cust_code
                continue

            #联系人名字放在部门字段
            department = contact_row.get('department') or ''
            link_name = contact_row.get('link_name') or ''
            if link_name:
                if department:
                    department += ' | '
                department += link_name

            if contact_row.get('cont_memo'):
                cont_vals = {
                    'name': contact_row.get('cont_memo') or False,
                    'previous_contact_description': contact_row.get('cont_uuid'),
                    'date': cont_date,
                    'user_id': user_id or False,
                    'opportunity_id': lead_id or False,
                    'department': department,
                    'duties': contact_row.get('link_duty') or False,
                    'street': contact_row.get('address') or False,
                    'partner_phone': contact_row.get('link_tel') or False,
                    'contacts_name': contact_row.get('link_name') or False,
                    'contact_result_type': contact_row.get('ring_result') or False,
                    'contact_type': contact_row.get('cont_kind') or False,
                }
                #检查联系记录是否存在
                pg_cr.execute("SELECT id FROM crm_phonecall WHERE previous_contact_description='%s'" % contact_row.get('cont_uuid'))
                phone_call_row = pg_cr.fetchone()
                print '---- link_name, id', contact_row.get('cust_code'), link_name, phone_call_row
                id = None
                if phone_call_row:
                    id = phone_call_row.get('id')
                    crm_phonecall_proxy.write(id, cont_vals)
                else:
                    id = crm_phonecall_proxy.create(cont_vals)
                #更新联系记录的最后写入时间和建立时间
                pg_cr.execute("UPDATE crm_phonecall SET create_date='%s', write_date='%s' WHERE id=%s" % (cont_date, cont_date, id))
                postgre.commit()

    return jsonify({'result': 'OK'})

#补充缺少地址的联系人或客户
@app.route('/update_partner_street')
def update_partner_street():
    with closing(postgre.cursor(cursor_factory = psycopg2.extras.RealDictCursor)) as pg_cr:
        my_cr = mysql.cursor(MySQLdb.cursors.DictCursor)
        sql = "SELECT rpc.id AS rpc_id, rp.id, rp.street, rp.cust_code FROM res_partner rpc INNER JOIN res_partner rp ON rpc.parent_id=rp.id WHERE rpc.contacts=TRUE AND (rpc.street='' OR rpc.street IS null) and rp.cust_code is not null"
        pg_cr.execute(sql)
        partner_rows = pg_cr.fetchall()
        for partner_row in partner_rows:
            partner_id = partner_row.get('id')
            rpc_id = partner_row.get('rpc_id')
            cust_code = partner_row.get('cust_code')
            my_cr.execute("SELECT address FROM td_customer WHERE cust_code='%s'" % cust_code)
            origin_cust_row = my_cr.fetchone()
            if origin_cust_row:
                address = origin_cust_row.get('address')
                if address:
                    print cust_code, address
                    pg_cr.execute("UPDATE res_partner SET street='%s' WHERE id=%s" % (address, rpc_id))
                    pg_cr.execute("UPDATE res_partner SET street='%s' WHERE id=%s" % (address, partner_id))
        postgre.commit()
    return jsonify({'result': 'OK'})

#去除多余的入库记录
@app.route('/remove_stock_move')
def remove_stock_move():
    with closing(postgre.cursor(cursor_factory = psycopg2.extras.RealDictCursor)) as pg_cr:
        sql = "SELECT po_id FROM"\
              " (SELECT pol.id AS pol_id, pol.order_id AS po_id, pol.product_qty, pol.product_id, delivered_qty, (SELECT sum("\
              " sm.product_qty) AS total FROM stock_move sm INNER JOIN stock_picking sp ON sm.picking_id=sp.id WHERE "\
              " sp.po_id=pol.order_id AND sm.product_id=pol.product_id AND sp.state<>'cancel') AS comp_delivered_qty FROM"\
              " purchase_order_line  pol) s "\
              " WHERE delivered_qty<>product_qty AND delivered_qty<>0 AND product_qty>0 GROUP BY po_id ORDER BY po_id"
        sql = "SELECT id as po_id FROM purchase_order WHERE id=788"
        pg_cr.execute(sql)
        po_id_rows = pg_cr.fetchall()
        for po_id_row in po_id_rows:
            po_id = po_id_row.get('po_id')
            print '------ po_id:', po_id
            pg_cr.execute("SELECT * FROM stock_picking WHERE po_id=%s AND state<>'cancel' ORDER BY create_date DESC " % po_id)
            picking_row = pg_cr.fetchone()
            picking_id = picking_row.get('id')
            print '------ picking_row id, name:', picking_id, picking_row.get('name')
            pg_cr.execute("SELECT quant_id, move_id FROM stock_quant_move_rel sqmr INNER JOIN stock_move sm ON sqmr.move_id=sm.id  WHERE sm.picking_id=%s" % picking_id)
            quant_id_rows = pg_cr.fetchall()
            for quant_id_row in quant_id_rows:
                quant_id = quant_id_row.get('quant_id')
                move_id = quant_id_row.get('move_id')
                print '------- quant_id, move_id:', quant_id, move_id
                #取得出库相关move_id
                pg_cr.execute("SELECT move_id FROM stock_quant_move_rel WHERE quant_id=%s AND move_id NOT IN (SELECT id FROM stock_move WHERE picking_id=%s)" % (quant_id, picking_id))
                move_id_rows = pg_cr.fetchall()
                for move_id_row in move_id_rows:
                    out_move_id = move_id_row.get('move_id')
                    print '-------------- out_move_id:', out_move_id
                else:
                    print u'没有出库相关move，可以简单的清理入库造成的库存影响'
                    # 删除quant_move_rel表的入库move相关记录
                    pg_cr.execute("DELETE FROM stock_quant_move_rel WHERE move_id = %s" % move_id)
                    # 删除stock_move表的出库picking相关记录
                    pg_cr.execute("DELETE FROM stock_move WHERE picking_id = %s" % picking_id)
                    # 删除stock_quant表跟入库picking_id相关的记录
                    pg_cr.execute("DELETE FROM stock_quant WHERE id = %s" % quant_id)
                    # 更新入库单状态为取消状态
                    pg_cr.execute("UPDATE stock_picking SET state='draft' WHERE id = %s" % picking_id)
    postgre.commit()
    return jsonify({'result': 'OK'})

#导入积分
@app.route('/import_point')
def import_point():
    with closing(postgre.cursor(cursor_factory = psycopg2.extras.RealDictCursor)) as pg_cr:
        point_events_proxy = Object(g.openerp_cnx, 'point.events')
        sql = "SELECT id FROM point_events WHERE event_code='DAILY_EVENTS'"
        pg_cr.execute(sql)
        event_row = pg_cr.fetchone()
        event_id = event_row.get('id')

        uid=1  #用户名admin所对应的id号
        pwd = DefaultConfig.OPENERP_DEFAULT_PASSWORD   #admin对应的密码
        db_name = DefaultConfig.OPENERP_DATABASE
        host = DefaultConfig.OPENERP_HOSTNAME
        tcp_port = DefaultConfig.OPENERP_PORT
        sock = xmlrpclib.ServerProxy("http://%s:%s/xmlrpc/object" % (host, tcp_port))

        model = 'point.events'  ##要运行哪个类中的函数？
        method = 'payment_point'     ##要运行的函数

        name_list = {u'李会文': 22,
            u'赵丽娜': 12,
            u'程俊': 15,
            u'王晓芳': 20,
            u'陈晴晴': 17,
            u'叶青': 20,
            u'徐琳': 5,
            u'姚利英': 15,
            u'吴立': 20,
            u'陆志宏': 17,
            u'杨帆': 7,
            u'解瑾': 15,
            u'郭言': 22,
            u'杜晓君': 22,
            u'聂芳芳': 15,
            u'王艳 ': 15,
            u'张宁': 20,
            u'肖金泉': 5,
            u'郑美慧': 17,
            u'卢盛娟': 17,
            u'王婧': 17,
            u'黄铮': 5,
            u'李佳俊': 5,
            u'张雪菲': 7,
            u'盛成': 12,
            u'李文斌': 7,
            u'刘明伟': 12,
            u'黄瑾晔': 7,
            u'赵甲': 2,
            u'黄僅捷': 2,
            u'袁智超': 2,
            u'丁明世': 2,
            u'黄蕊': 2,
            u'张丽娟': 2,
            u'蔡维维': 2,
            u'唐瑶': 5,
            u'余斌俊': 5,
        }
        for name in name_list:
            sql = "SELECT he.address_home_id FROM hr_employee he INNER JOIN res_partner rp ON he.address_home_id=rp.id WHERE he.name_related='%s'" % (name,)
            pg_cr.execute(sql)
            employee_row = pg_cr.fetchone()
            if employee_row:
                partner_id = employee_row.get('address_home_id')
                print name, partner_id
                event_point = name_list.get(name)
                if partner_id:
                    result = sock.execute(db_name, uid, pwd, model, method, event_id, event_point, partner_id, u'2017年5月')
                    print "\n==== result: ", result
            else:
                print name, u'员工不存在'
        return jsonify({'result': 'OK'})

class TestViews(TestCase):
    def create_app(self):
        app.config['TESTING'] = True
        return app

    # def test_sync_01_user(self):
    #     response = self.client.get("/user")
    #     print u'------- 用户同步结束'
    #     self.assertEquals(response.json, {'result': 'OK'})
    #
    #def test_sync_02_customer(self):
    #    response = self.client.get("/customer")
    #    print u'------- 客户同步结束'
    #    self.assertEquals(response.json, {'result': 'OK'})

    # def test_sync_03_supplier(self):
    #     response = self.client.get("/supplier")
    #     print u'------- 供应商同步结束'
    #     self.assertEquals(response.json, {'result': 'OK'})

    # def test_sync_04_product_cate(self):
    #     response = self.client.get("/product_cate")
    #     print u'------- 同步产品分类结束'
    #
    # def test_sync_05_product_uom(self):
    #     response = self.client.get("/product_uom")
    #     print u'------- 同步产品单位结束'

    # def test_sync_06_products(self):
    #     response = self.client.get("/products")
    #     print u'------- 同步产品结束'

    # def test_sync_07_order(self):
    #     response = self.client.get("/orders")
    #     print u'------- 订单同步结束'

    # def test_sync_08_contact_record(self):
    #     response = self.client.get("/contact_record")
    #     print u'------- 联系记录同步结束'

    # def test_sync_09_address_moveto_contacts(self):
    #     response = self.client.get("/address_moveto_contacts")
    #     print u'------- 移动收货地址到重要联系人结束'

    # def test_sync_09_leads(self):
    #     response = self.client.get("/leads")
    #     print u'------- 导入线索leads结束'

    # def test_sync_10_leads(self):
    #     response = self.client.get("/lead_contact_record")
    #     print u'------- 导入线索联系记录结束'

    # def test_sync_11_update_partner_street(self):
    #     response = self.client.get("/update_partner_street")
    #     print u'------- 补充partner地址结束'

    # def test_sync_12_remove_stock_move(self):
    #     response = self.client.get("/remove_stock_move")
    #     print u'----------- 移除多余的入库记录结束'

    #导入积分
    def test_sync_13_import_point(self):
        response = self.client.get("/import_point")
        print u'----------- 导入积分结束'

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5001)
    #unittest.main()